﻿Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data.SqlTypes
Public Class Connection
    Private sConn As SqlConnection
    Private sConnSAP As SqlConnection
    Private conString As String
    Public bConnect As Boolean
    Public Function setSQLDB(ByVal DatabaseName As String) As String
        Try
            Dim SQLUser, SQLPwd, SQLServer, LicenseServer As String
            Dim strConnect As String = String.Empty
            Dim sCon As String = String.Empty
            Dim SQLType As String = String.Empty
            Dim MyArr As Array
            Dim sErrMsg As String = String.Empty
            strConnect = "DBConnect"
            sCon = System.Configuration.ConfigurationManager.AppSettings.Get(strConnect)
            MyArr = sCon.Split(";")
            If MyArr.Length > 0 Then

                SQLServer = MyArr(3).ToString()
                SQLUser = MyArr(4).ToString()
                SQLPwd = MyArr(5).ToString()
                LicenseServer = MyArr(6).ToString()

                sCon = "server= " + SQLServer + ";database=" + DatabaseName + " ;uid=" + SQLUser + "; pwd=" + SQLPwd + ";"
                conString = sCon
                sConnSAP = New SqlConnection(sCon)
            End If
        Catch ex As Exception
            Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex)
        End Try
        Return String.Empty
    End Function
#Region "ADO SAP"
    Private Function GetConnectionString_SAP() As SqlConnection
        If sConnSAP.State = ConnectionState.Open Then
            sConnSAP.Close()
        End If
        Try
            If sConnSAP.ConnectionString.Length = 0 Then
                sConnSAP.ConnectionString = conString
            End If
            sConnSAP.Open()
        Catch ex As Exception
            'WriteLog(ex.ToString)
        End Try
        Return sConnSAP
    End Function
    Public Function ObjectGetAll_Stored_SAP(ByVal storedName As String, ByVal ParamArrays As Dictionary(Of String, String)) As DataSet
        Try
            Using myConn = GetConnectionString_SAP()
                Dim MyCommand As SqlCommand = New SqlCommand(storedName, myConn)
                MyCommand.CommandType = CommandType.StoredProcedure

                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataSet = New DataSet()
                da.SelectCommand = MyCommand
                For Each item As KeyValuePair(Of String, String) In ParamArrays
                    MyCommand.Parameters.AddWithValue(item.Key, item.Value)
                Next
                da.Fill(mytable)
                myConn.Close()
                Return mytable
            End Using
        Catch ex As Exception
            'WriteLog(ex.ToString)
            Return Nothing
        End Try
    End Function
    Public Function ObjectGetAll_Stored_SAP(ByVal storedName As String) As DataSet
        Try
            Using myConn = GetConnectionString_SAP()
                Dim MyCommand As SqlCommand = New SqlCommand(storedName, myConn)
                MyCommand.CommandType = CommandType.StoredProcedure

                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataSet = New DataSet()
                da.SelectCommand = MyCommand
                da.Fill(mytable)
                myConn.Close()
                Return mytable
            End Using
        Catch ex As Exception
            Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex)

            Return Nothing
        End Try
    End Function
    Public Function ObjectGetAll_Query_SAP(ByVal QueryString As String) As DataSet
        Dim strCon As String = ""
        Try
            Using myConn = GetConnectionString_SAP()
                strCon = myConn.ConnectionString
                Dim MyCommand As SqlCommand = New SqlCommand(QueryString, myConn)
                MyCommand.CommandType = CommandType.Text
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataSet = New DataSet()
                da.SelectCommand = MyCommand
                da.Fill(mytable)
                myConn.Close()
                Return mytable
            End Using
        Catch ex As Exception
            'WriteLog(ex.ToString & "_" & QueryString & "_" & strCon)
            Return Nothing
        End Try
    End Function
    Public Function ObjectGetAll_Query_SAP(ByVal QueryString As String, ByVal ParamArrays As Array) As DataSet
        Dim strCon As String = ""
        Try
            Using myConn = GetConnectionString_SAP()
                strCon = myConn.ConnectionString
                Dim MyCommand As SqlCommand = New SqlCommand(QueryString, myConn)
                MyCommand.CommandType = CommandType.Text
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataSet = New DataSet()
                da.SelectCommand = MyCommand
                For i As Integer = 0 To ParamArrays.Length - 1
                    MyCommand.Parameters.AddWithValue(String.Format("{0}{1}", "@Param", i + 1), ParamArrays(i))
                Next
                da.Fill(mytable)
                myConn.Close()
                Return mytable
            End Using
        Catch ex As Exception
            'WriteLog(ex.ToString & "_" & QueryString & "_" & strCon)
            Return Nothing
        End Try
    End Function
    Public Function ObjectGetAll_Query_Dynamic(ByVal QueryString As String, ByVal ParamArrays As Dictionary(Of String, Object)) As DataSet
        Dim strCon As String = ""
        Try
            Using myConn = GetConnectionString_SAP()
                strCon = myConn.ConnectionString
                Dim MyCommand As SqlCommand = New SqlCommand(QueryString, myConn)
                MyCommand.CommandType = CommandType.Text
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataSet = New DataSet()
                da.SelectCommand = MyCommand
                For Each i As KeyValuePair(Of String, Object) In ParamArrays
                    MyCommand.Parameters.AddWithValue(i.Key, i.Value)
                Next
                da.Fill(mytable)
                myConn.Close()
                Return mytable
            End Using
        Catch ex As Exception
            'WriteLog(ex.ToString & "_" & QueryString & "_" & strCon)
            Return Nothing
        End Try
    End Function
    Public Function Object_Execute_SAP(ByVal Query As String, ByVal ParamArrays As Array) As Integer
        Dim strCon As String = ""
        Try
            Using myConn = GetConnectionString_SAP()
                strCon = myConn.ConnectionString
                Dim MyCommand As SqlCommand = New SqlCommand(Query, myConn)
                MyCommand.CommandType = CommandType.Text
                For i As Integer = 0 To ParamArrays.Length - 1
                    MyCommand.Parameters.AddWithValue(String.Format("{0}{1}", "@Param", i + 1), ParamArrays(i))
                Next
                Dim count As Integer = MyCommand.ExecuteNonQuery()
                myConn.Close()
                Return count
            End Using
        Catch ex As Exception
            Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex)
            Return 0
        End Try
    End Function
    Public Function Object_Execute_Stored(ByVal storeName As String, ByVal ParamArrays As Dictionary(Of String, String)) As Integer
        Dim strCon As String = ""
        Try
            Using myConn = GetConnectionString_SAP()
                strCon = myConn.ConnectionString
                Dim MyCommand As SqlCommand = New SqlCommand(storeName, myConn)
                MyCommand.CommandType = CommandType.StoredProcedure
                For Each item As KeyValuePair(Of String, String) In ParamArrays
                    MyCommand.Parameters.AddWithValue(item.Key, item.Value)
                Next
                Dim count As Integer = MyCommand.ExecuteNonQuery()
                myConn.Close()
                Return count
            End Using
        Catch ex As Exception
            Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex)
            Throw ex
            Return -2
        End Try
    End Function
    Public Function Object_Execute_SAP(ByVal Query As String, ByVal docEntry As Integer, ByVal entries As IEnumerable(Of oDocLine.oLine)) As Integer
        Dim strCon As String = ""
        Try
            Using myConn = GetConnectionString_SAP()
                Dim MyCommand As SqlCommand = New SqlCommand()
                MyCommand.CommandType = CommandType.Text
                Dim totalQuery As String = String.Empty
                Dim index As Integer = 0
                For Each line In entries
                    totalQuery += String.Format(Query, index)
                    MyCommand.Parameters.AddWithValue(String.Format("@DocEntry{0}", index), docEntry)
                    MyCommand.Parameters.AddWithValue(String.Format("@ItemID{0}", index), line.ItemID)
                    MyCommand.Parameters.AddWithValue(String.Format("@ItemCode{0}", index), line.ItemCode)
                    MyCommand.Parameters.AddWithValue(String.Format("@ItemName{0}", index), line.ItemName)
                    MyCommand.Parameters.AddWithValue(String.Format("@WarehouseID{0}", index), line.WarehouseID)
                    MyCommand.Parameters.AddWithValue(String.Format("@Quantity{0}", index), line.Quantity)
                    MyCommand.Parameters.AddWithValue(String.Format("@BasePrice{0}", index), line.BasePrice)
                    MyCommand.Parameters.AddWithValue(String.Format("@LineTotal{0}", index), line.LineTotal)
                    MyCommand.Parameters.AddWithValue(String.Format("@SalesPersonID{0}", index), line.SalesPersonID)
                    MyCommand.Parameters.AddWithValue(String.Format("@ItemType{0}", index), line.ItemType)
                    'MyCommand.Parameters.AddWithValue(String.Format("@DiscountPercent{0}", index), line.DiscountPercent)
                    MyCommand.Parameters.AddWithValue(String.Format("@DiscountAmount{0}", index), line.DiscountAmount)
                    'MyCommand.Parameters.AddWithValue(String.Format("@PriceAfterDiscount{0}", index), line.PriceAfterDiscount)
                    MyCommand.Parameters.AddWithValue(String.Format("@TotalAfterDiscount{0}", index), line.TotalAfterDiscount)
                    index += 1
                Next
                MyCommand.Connection = myConn
                MyCommand.CommandText = totalQuery
                Dim count As Integer = MyCommand.ExecuteNonQuery()
                myConn.Close()
                Return count
            End Using
        Catch ex As Exception
            Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex)
            Return 0
        End Try
    End Function
    Public Function Object_Execute_For_Report(ByVal query As String, ByVal reportID As Integer, ByVal entries As IEnumerable(Of oReportParameter.oLineReportParameter)) As Integer
        Dim strCon As String = ""
        Try
            Using myConn = GetConnectionString_SAP()
                Dim MyCommand As SqlCommand = New SqlCommand()
                MyCommand.CommandType = CommandType.Text
                Dim totalQuery As String = String.Empty
                Dim index As Integer = 0
                For Each line In entries
                    totalQuery += String.Format(query, index)
                    MyCommand.Parameters.AddWithValue(String.Format("@ReportID{0}", index), reportID)
                    MyCommand.Parameters.AddWithValue(String.Format("@ParaName{0}", index), line.ParaName)
                    MyCommand.Parameters.AddWithValue(String.Format("@ParaCaption{0}", index), line.ParaCaption)
                    MyCommand.Parameters.AddWithValue(String.Format("@ParaType{0}", index), line.ParaType)
                    MyCommand.Parameters.AddWithValue(String.Format("@ParaFromQuery{0}", index), line.ParaFromQuery)
                    index += 1
                Next
                MyCommand.Connection = myConn
                MyCommand.CommandText = totalQuery
                Dim count As Integer = MyCommand.ExecuteNonQuery()
                myConn.Close()
                Return count
            End Using
        Catch ex As Exception
            Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex)
            Return 0
        End Try
    End Function
    Public Function Object_Execute_SAP(ByVal Query As String) As Integer
        Dim strCon As String = ""
        Try
            Using myConn = GetConnectionString_SAP()
                strCon = myConn.ConnectionString
                Dim MyCommand As SqlCommand = New SqlCommand(Query, myConn)
                MyCommand.CommandType = CommandType.Text
                Dim count As Integer = MyCommand.ExecuteNonQuery()
                myConn.Close()
                Return count
            End Using
        Catch ex As Exception
            Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex)
            Return 0
        End Try
    End Function

    Public Function Object_ExecuteScalar_SAP(ByVal Query As String) As Integer
        Dim strCon As String = ""
        Try
            Using myConn = GetConnectionString_SAP()
                strCon = myConn.ConnectionString
                Dim MyCommand As SqlCommand = New SqlCommand(Query, myConn)
                MyCommand.CommandType = CommandType.Text
                Dim count As Long = MyCommand.ExecuteScalar()
                myConn.Close()
                Return count
            End Using
        Catch ex As Exception
            Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex)
            Return 0
        End Try
    End Function

    Public Function Object_ExecuteScalar_EmailIssue(ByVal Query As String, ByVal Identify As String) As String
        Dim strCon As String = ""
        Try
            Using myConn = GetConnectionString_SAP()
                strCon = myConn.ConnectionString
                Dim MyCommand As SqlCommand = New SqlCommand(Query, myConn)
                MyCommand.CommandType = CommandType.Text
                Dim count As Long = MyCommand.ExecuteScalar()
                myConn.Close()
                Return count
            End Using
        Catch ex As Exception
            Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex)
            If (Identify = "Insert") Then
                Return "InsertError " & ex.Message
            ElseIf (Identify = "Update") Then
                Return "UpdateError " & ex.Message
            End If
        End Try
        Return String.Empty
    End Function
#End Region
    Public Sub ConnectDB()
        Try
            Dim strConnect As String = String.Empty
            Dim sCon As String = String.Empty
            Dim MyArr As Array
            strConnect = "DBConnect"
            sCon = System.Configuration.ConfigurationSettings.AppSettings.Get(strConnect)
            MyArr = sCon.Split(";")
            Dim DatabaseName, SQLUser, SQLPwd, SQLServer As String

            DatabaseName = MyArr(0).ToString()
            SQLServer = MyArr(3).ToString()
            SQLUser = MyArr(4).ToString()
            SQLPwd = MyArr(5).ToString()

            sCon = "server= " + SQLServer + ";database=" + DatabaseName + " ;uid=" + SQLUser + "; pwd=" + SQLPwd + ";"
            sConn = New SqlConnection(sCon)
        Catch ex As Exception
            Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex)
        End Try
    End Sub
    Private Function GetConnectionString_DB() As SqlConnection
        If sConn.State = ConnectionState.Open Then
            sConn.Close()
        End If
        Try
            sConn.Open()
        Catch ex As Exception
            Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex)
            Return Nothing
        End Try
        Return sConn
    End Function
    Public Function ObjectGetAll_Query_DB(ByVal QueryString As String) As DataSet
        Try
            Using myConn = GetConnectionString_DB()
                Dim MyCommand As SqlCommand = New SqlCommand(QueryString, myConn)
                MyCommand.CommandType = CommandType.Text
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataSet = New DataSet()
                da.SelectCommand = MyCommand
                da.Fill(mytable)
                myConn.Close()
                Return mytable
            End Using
        Catch ex As Exception
            Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex)
            Return Nothing
        End Try
    End Function
    Public Function ObjectGetAll_Query_DB(ByVal QueryString As String, ByVal ParamArrays As Array) As DataSet
        Try
            Using myConn = GetConnectionString_DB()
                Dim MyCommand As SqlCommand = New SqlCommand(QueryString, myConn)
                MyCommand.CommandType = CommandType.Text
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataSet = New DataSet()
                da.SelectCommand = MyCommand
                For i As Integer = 0 To ParamArrays.Length - 1
                    MyCommand.Parameters.AddWithValue(String.Format("{0}{1}", "@Param", i + 1), ParamArrays(i))
                Next
                da.Fill(mytable)
                myConn.Close()
                Return mytable
            End Using
        Catch ex As Exception
            Log.WriteException(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType, "SystemError", ex)
            Return Nothing
        End Try
    End Function
End Class
